#!/usr/bin/env node

// Staging数据库连接测试脚本
// 使用方法: node scripts/staging/test-staging-database.js

const mysql = require('mysql2/promise');
require('dotenv').config({ path: './backend/.env.staging' });

const config = {
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectTimeout: 10000,
  acquireTimeout: 10000,
  timeout: 10000
};

const readOnlyConfig = {
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT),
  user: 'cmb_staging_readonly',
  password: 'ReadOnly123!',
  database: process.env.DB_NAME,
  connectTimeout: 10000
};

async function testConnection(config, userType) {
  try {
    console.log(`\n🔍 测试${userType}连接...`);
    console.log(`Host: ${config.host}:${config.port}`);
    console.log(`Database: ${config.database}`);
    console.log(`User: ${config.user}`);
    
    const connection = await mysql.createConnection(config);
    
    // 基本连接测试
    const [versionResult] = await connection.execute('SELECT VERSION() as version, NOW() as current_time, DATABASE() as current_db');
    console.log(`✅ ${userType}连接成功`);
    console.log(`   数据库版本: ${versionResult[0].version}`);
    console.log(`   当前数据库: ${versionResult[0].current_db}`);
    console.log(`   服务器时间: ${versionResult[0].current_time}`);
    
    // 权限测试
    try {
      const [tables] = await connection.execute(`
        SELECT COUNT(*) as table_count 
        FROM information_schema.tables 
        WHERE table_schema = ?`, [config.database]);
      console.log(`   数据库表数量: ${tables[0].table_count}`);
    } catch (error) {
      console.log(`   ⚠️ 无法查询表信息: ${error.message}`);
    }
    
    // 写权限测试（仅对完全权限用户）
    if (userType === '完全权限用户') {
      try {
        await connection.execute(`
          CREATE TABLE IF NOT EXISTS staging_test (
            id INT AUTO_INCREMENT PRIMARY KEY,
            test_data VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          )`);
        
        await connection.execute(`
          INSERT INTO staging_test (test_data) VALUES (?)`, 
          [`测试数据 - ${new Date().toISOString()}`]);
        
        const [testResult] = await connection.execute('SELECT COUNT(*) as count FROM staging_test');
        console.log(`   ✅ 写权限测试通过，测试表记录数: ${testResult[0].count}`);
        
        // 清理测试数据
        await connection.execute('DROP TABLE IF EXISTS staging_test');
        console.log(`   🧹 测试表已清理`);
      } catch (error) {
        console.log(`   ❌ 写权限测试失败: ${error.message}`);
      }
    }
    
    await connection.end();
    return true;
  } catch (error) {
    console.log(`❌ ${userType}连接失败: ${error.message}`);
    console.log(`   错误代码: ${error.code}`);
    if (error.code === 'ECONNREFUSED') {
      console.log(`   💡 建议: 检查数据库服务器是否运行，端口是否开放`);
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      console.log(`   💡 建议: 检查用户名和密码是否正确`);
    } else if (error.code === 'ENOTFOUND') {
      console.log(`   💡 建议: 检查数据库主机地址是否正确`);
    }
    return false;
  }
}

async function main() {
  console.log('🗄️ Staging数据库连接测试');
  console.log('=' .repeat(50));
  
  const results = [];
  
  // 测试完全权限用户连接
  results.push(await testConnection(config, '完全权限用户'));
  
  // 测试只读用户连接
  results.push(await testConnection(readOnlyConfig, '只读用户'));
  
  console.log('\n📊 测试总结');
  console.log('=' .repeat(50));
  const successCount = results.filter(r => r).length;
  const totalCount = results.length;
  console.log(`成功: ${successCount}/${totalCount}`);
  
  if (successCount === totalCount) {
    console.log('🎉 所有数据库连接测试通过！');
    process.exit(0);
  } else {
    console.log('❌ 部分数据库连接测试失败，请检查配置');
    process.exit(1);
  }
}

// 处理未捕获的异常
process.on('unhandledRejection', (error) => {
  console.error('❌ 未处理的异常:', error.message);
  process.exit(1);
});

main();
